/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package shopnav;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.CallableStatement;
/**
 *
 * @author skysoft
 */
public class clsDatabase {
    /* String url=System.getProperty("dbname");
     String user=System.getProperty("user");
     String passwd=System.getProperty("passwd");
     String driver=System.getProperty("driver");*/

   String url="jdbc:mysql://localhost:3306/shopnav";
   String user="root";
   String passwd="";
   String driver="com.mysql.jdbc.Driver";
   
     Connection conn=null;
     ResultSet rset=null;
     CallableStatement cs=null;

     Connection getConnection()
    {
         try
         {
             Class.forName(driver);
             conn=DriverManager.getConnection(url, user, passwd);
         }catch(ClassNotFoundException ex)
         {
               System.err.println(ex.getMessage());
         }catch(SQLException ex)
         {
           System.err.println(ex.getMessage());
         }
         return conn;
     }
    public int addMall(Mall ml)
    {
        int rs=0;
        
        try
        {
            conn=getConnection();
            cs=conn.prepareCall("call sp_ins_mall(?,?,?,?,?);");
            cs.setString(1,ml.getMallName());
            cs.setString(2,ml.getFloorPlan().toString());
            cs.setString(3,ml.getLon().toString()+","+ml.getLat().toString());
            cs.setString(4,ml.getMalladdress());
            cs.setString(5,String.valueOf(ml.getMallid()));
            rs=cs.executeUpdate();
            conn.close();
        }catch(Exception ex)
        {
            System.out.println(ex.getMessage());
        }
        return rs;
    }
    //add new shop
    public int addShop(Shop sh)
    {
        int rs=0;
        try
        {
            conn=getConnection();
            cs=conn.prepareCall("call sp_ins_shop(?,?,?,?)");
            cs.setInt(1,sh.getShopNo());
            cs.setString(2,sh.getShopName());
            cs.setString(3,sh.getCategory());
            cs.setInt(4,sh.getMallid());
            rs=cs.executeUpdate();
            conn.close();
        }catch(Exception ex)
        {
            System.out.println(ex.getMessage());
        }
        return rs;
    }
    //add new product

    public int addProduct(Product prdt)
    {
        int rs=0;
        try
        {
            conn=getConnection();
            cs=conn.prepareCall("call sp_ins_product(?,?,?,?,?);");
            cs.setString(1,prdt.getProductId());
            cs.setString(2,prdt.getProName());
            cs.setDouble(3,prdt.getProPrice());
            cs.setString(4,prdt.getProCategory());
            cs.setInt(5,prdt.getShopid());
            rs=cs.executeUpdate();
            conn.close();
        }catch(Exception ex)
        {
            System.out.println(ex.getMessage());
        }
        return rs;
    }
    //get all malls

    public Mall[] getMalls()
    {
        Mall [] arrMall=null;
        try
        {
            conn=getConnection();
            cs=conn.prepareCall("call sp_selall_mall();");
            rset=cs.executeQuery();

            int num=rset.getFetchSize();
            int i=0;
            if(num > 0)
            {
                arrMall=new Mall[num];
                while(rset.next())
                {
                    arrMall[i].setMallName(rset.getString("Name").trim());
                    arrMall[i].setFloorPlan(rset.getBytes("FloorPlan"));
                    arrMall[i].setLon(Double.parseDouble(rset.getString("Location").split(",")[0]));
                    arrMall[i].setLat(Double.parseDouble(rset.getString("Location").split(",")[1]));
                    arrMall[i].setAddress(rset.getString("Address"));
                    arrMall[i].setMallid(rset.getInt("MallId"));
                    i++;
                }
            }
            rset.close();
            conn.close();
        }catch(Exception ex)
        {
            System.out.println(ex.getMessage());
        }
        return arrMall;
    }
    //get all shops per mall

    public Shop[] getShops(String mallId)
    {
        Shop [] arrShop=null;
        try
        {
            conn=getConnection();
            cs=conn.prepareCall("call sp_sel_shop_by_mall(?);");
            cs.setString(1, mallId);
            rset=cs.executeQuery();
            int num=rset.getFetchSize();
            int i=0;
            if(num > 0)
            {
                arrShop=new Shop[num];
                while(rset.next())
                {
                    arrShop[i].setShopNo(rset.getInt("ShopId"));
                    arrShop[i].setShopName(rset.getString("ShopName"));
                    arrShop[i].setAddress(rset.getString("Address"));
                    arrShop[i].setMallid(rset.getInt("MallId"));
                    i++;

                }
            }
            rset.close();
            conn.close();
        }catch(Exception ex)
        {
            System.out.println(ex.getMessage());
        }
        return arrShop;
    }
    //get all shops in the system

    public Shop[] getAllShops()
    {
        Shop[] arrShops=null;
        try
        {
            conn=getConnection();
            cs=conn.prepareCall("call sp_selall_shop();");
            rset=cs.executeQuery();
            int num=rset.getFetchSize();
            if(num > 0)
            {
                arrShops=new Shop[num];
                int i=0;
                while(rset.next())
                {
                    arrShops[i].setShopNo(rset.getInt("ShopId"));
                    arrShops[i].setShopName(rset.getString("ShopName"));
                    arrShops[i].setCategory(rset.getString("Category"));
                    arrShops[i].setMallid(rset.getInt("MallId"));
                    i++;
                }
            }
            rset.close();
            conn.close();
        }catch(Exception ex)
        {
            System.err.println(ex.getMessage());
        }
        return arrShops;
    }
    //get all products per shop

    public Product[] getProducts(int shopId)
    {
        Product [] arrPrd=null;
        try
        {
            conn=getConnection();
            cs=conn.prepareCall("call sp_sel_product_shop(?);");
            rset=cs.executeQuery();
            int num=rset.getFetchSize();
            if(num > 0)
            {
                int i=0;
                arrPrd=new Product[num];
                while(rset.next())
                {
                    arrPrd[i].setProductId(rset.getString("ProductId"));
                    arrPrd[i].setProName(rset.getString("ProductName"));
                    arrPrd[i].setProPrice(rset.getDouble("Price"));
                    arrPrd[i].setProCategory(rset.getString("Category").trim());
                    arrPrd[i].setShopid(rset.getInt("ShopId"));
                    i++;
                }
            }
            rset.close();
            conn.close();
        }catch(Exception ex)
        {
            System.err.println(ex.getMessage());
        }
        return arrPrd;
    }
    //get all products....

    public Product[] getAllProducts()
    {
        Product [] arrPrd=null;
        try
        {
            conn=getConnection();
            cs=conn.prepareCall("call sp_selall_products();");
            rset=cs.executeQuery();
            int num=rset.getFetchSize();
            if(num > 0)
            {
                arrPrd=new Product[num];
                int i=0;
                while(rset.next())
                {
                    arrPrd[i].setProductId(rset.getString("ProductId"));
                    arrPrd[i].setProName(rset.getString("ProductName").trim());
                    arrPrd[i].setProPrice(rset.getDouble("Price"));
                    arrPrd[i].setProCategory(rset.getString("Category").trim());
                    arrPrd[i].setShopid(rset.getInt("ShopId"));
                    i++;
                }
            }
            rset.close();
            conn.close();
        }catch(Exception ex)
        {
            System.err.println(ex.getMessage());
        }
        return arrPrd;
    }
    //get products per category
    public Product[] getProductsByCat(String cat)
    {
        Product [] arrPrd=null;
        try
        {
            conn=getConnection();
            cs=conn.prepareCall("call sp_sel_product_category(?);");
            cs.setString(1, cat);
            rset=cs.executeQuery();
            int num=rset.getFetchSize();
            if(num > 0)
            {
                arrPrd=new Product[num];
                int i=0;
                while(rset.next())
                {
                     arrPrd[i].setProductId(rset.getString("ProductId"));
                    arrPrd[i].setProName(rset.getString("ProductName").trim());
                    arrPrd[i].setProPrice(rset.getDouble("Price"));
                    arrPrd[i].setProCategory(rset.getString("Category").trim());
                    arrPrd[i].setShopid(rset.getInt("ShopId"));
                    i++;
                }
            }
            rset.close();
            conn.close();
        }catch(Exception ex)
        {
            System.err.println(ex.getMessage());
        }
        return arrPrd;
    }
    //get products by category and shop

    public Product[] getProductsByCatShop(String cat,int shopId)
    {
        Product [] arrPrd=null;
        try
        {
            conn=getConnection();
            cs=conn.prepareCall("call sp_sel_product_cat_shop(?,?);");
            cs.setString(1, cat);
            cs.setInt(2, shopId);
            rset=cs.executeQuery();
            int num=rset.getFetchSize();
            if(num > 0)
            {
                arrPrd=new Product[num];
                int i=0;
                while(rset.next())
                {
                     arrPrd[i].setProductId(rset.getString("ProductId"));
                    arrPrd[i].setProName(rset.getString("ProductName").trim());
                    arrPrd[i].setProPrice(rset.getDouble("Price"));
                    arrPrd[i].setProCategory(rset.getString("Category").trim());
                    arrPrd[i].setShopid(rset.getInt("ShopId"));
                    i++;
                }
            }
            rset.close();
            conn.close();
        }catch(Exception ex)
        {
            System.err.println(ex.getMessage());
        }
        return arrPrd;
    }
    //get shops by category

    public Shop[] getShopsByCat(String cat)
    {
        Shop [] arrShop=null;
        try
        {
            conn=getConnection();
            cs=conn.prepareCall("call sp_sel_shop_by_category(?);");
            cs.setString(1, cat);
            rset=cs.executeQuery();
            int num=rset.getFetchSize();
            if(num > 0)
            {
                arrShop=new Shop[num];
                int i=0;
                while(rset.next())
                {
                    arrShop[i].setShopNo(rset.getInt("ShopId"));
                    arrShop[i].setShopName(rset.getString("ShopName"));
                    arrShop[i].setCategory(rset.getString("Category"));
                    arrShop[i].setMallid(rset.getInt("MallId"));
                    i++;
                }
            }
            rset.close();
            conn.close();
        }catch(Exception ex)
        {
            System.err.println(ex.getMessage());
        }
        return arrShop;
    }
    //get shop by category and mall

    public Shop[] getShopsByCatMall(String cat,int mallId)
    {
        Shop [] arrShop=null;
        try
        {
            conn=getConnection();
            cs=conn.prepareCall("call sp_sel_shop_cat_mall(?,?);");
            cs.setString(1, cat);
            cs.setInt(2, mallId);
            rset=cs.executeQuery();
            int num=rset.getFetchSize();
            if(num > 0)
            {
                arrShop=new Shop[num];
                int i=0;
                while(rset.next())
                {
                   arrShop[i].setShopNo(rset.getInt("ShopId"));
                    arrShop[i].setShopName(rset.getString("ShopName"));
                    arrShop[i].setCategory(rset.getString("Category"));
                    arrShop[i].setMallid(rset.getInt("MallId"));
                    i++;
                }
            }
            rset.close();
            conn.close();
        }catch(Exception ex)
        {
            System.err.println(ex.getMessage());
        }
        return arrShop;
    }
    //add shoppers history

    public int addShopperHistory()
    {
        int rs=0;
        return rs;
    }

    public void getShopperHistory(String userid)
    {

    }
    public User loginUser(String userId,String passwd)
    {
        User usr=null;
        try
        {
            conn=getConnection();
            cs=conn.prepareCall("call sp_login_user(?,?);");
            cs.setString(1, userId);
            cs.setString(2,passwd);
            rset=cs.executeQuery();
            if(rset.getFetchSize()>0)
            {
                usr=new User();
            while(rset.next())
            {
                usr.setUserId(rset.getString("Id").trim());
                 usr.setEmail(rset.getString("Email").trim());
                usr.setLname(rset.getString("Lname").trim());
                usr.setMName(rset.getString("FName").trim());
               
                usr.setDBirth(rset.getString("dob").trim());
                usr.setPassword(rset.getString("Password").trim());
            }
            }
            rset.close();
            conn.close();
        }catch(Exception ex)
        {
            System.err.println(ex.getMessage());
        }
        return usr;
    }
    public int saveUser(User usr)
    {
        int rs=0;
        try
        {
            conn=getConnection();
            cs=conn.prepareCall("call sp_ins_user(?,?,?,?,?)");
            cs.setString(1,usr.getLname());
            cs.setString(2,usr.getMName());
            cs.setString(3,usr.getEmail());
            cs.setString(4,usr.getDBirth());
            cs.setString(5,usr.getNickname());
            rs=cs.executeUpdate();
        }catch(Exception ex)
        {
            System.err.println(ex.getMessage());
        }
        return rs;
    }
}
